import csv


def process_ratio(ratio_str):
    """处理师生比字符串，提取数值部分并计算估算学生数"""
    if ":" in ratio_str:
        try:
            # 分割师生比，假设格式为 "X:XX"，取后段数值作为学生与教职工的比例
            _, student_part = ratio_str.split(":", 1)
            student_ratio = int(student_part.strip())
            return student_ratio
        except (ValueError, IndexError):
            return None
    return None


data = []
with open("baoshan-schools-2020.csv", mode="r", encoding="utf-8") as file:
    reader = csv.reader(file)
    header = next(reader)  # 读取表头
    for row in reader:
        if not row:  # 跳过空行
            continue
        serial_no = row[0].strip()
        school_type = row[2].strip()
        staff_count = row[14].strip()
        teacher_count = row[15].strip()
        ratio_raw = row[16].strip()

        # 筛选条件：公办学校、师生比有效、数值字段非空
        if (school_type == "公办" and
                serial_no != "" and
                staff_count.isdigit() and
                teacher_count.isdigit() and
                "1:" in ratio_raw):
            student_ratio = process_ratio(ratio_raw)
            if student_ratio is not None:
                estimated_student = int(staff_count) * student_ratio  # 计算估算学生数
                data.append([
                    serial_no,
                    row[1].strip(),  # 学校名称
                    staff_count,
                    teacher_count,
                    estimated_student
                ])

# 定义最终列名和顺序
final_columns = ["serial_no", "school_name", "total_staff_count", "full_time_teacher_count", "estimated_student_count"]
filtered_data = [final_columns]

# 过滤无效行（确保数值字段正确）
for row in data:
    try:
        # 验证数值字段（避免非数字字符）
        serial_no, school_name, staff, teacher, estimated = row
        int(staff), int(teacher), int(estimated)
        filtered_data.append(row)
    except ValueError:
        continue  # 跳过包含非数字的行

# 保存结果到 CSV
with open("clean-baoshan-schools-2020.csv", mode="w", encoding="utf-8", newline='') as file:
    writer = csv.writer(file)
    writer.writerows(filtered_data)

for line in filtered_data:
    print(",".join(map(str, line)))